package com.ruoyi.finance.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.ruoyi.commodity.domain.Product;
import com.ruoyi.common.constant.Constants;
import com.ruoyi.common.core.domain.Oem;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.depot.domain.WalmartStockProductDetails;
import com.ruoyi.depot.mapper.WalmartStockProductDetailsMapper;
import com.ruoyi.finance.domain.FeeDistributor;
import com.ruoyi.finance.domain.WalmartProfitReport;
import com.ruoyi.finance.domain.WalmartProfitReportDetail;
import com.ruoyi.finance.mapper.WalmartProfitReportDetailMapper;
import com.ruoyi.finance.mapper.WalmartProfitReportMapper;
import com.ruoyi.finance.service.WalmartProfitReportDetailService;
import com.ruoyi.finance.service.WalmartProfitReportService;
import com.ruoyi.walmart.domain.Items.Items;
import com.ruoyi.walmart.domain.auth.ShopAuth;
import com.ruoyi.walmart.domain.shipments.ShipmentMatch;
import com.ruoyi.walmart.domain.shipments.ShipmentStock;
import com.ruoyi.walmart.domain.shipments.dto.CostFreightPair;
import com.ruoyi.walmart.domain.shipments.dto.SkuGtinKey;
import com.ruoyi.walmart.domain.shipments.dto.WalmartProfitReportDetailUpdate;
import com.ruoyi.walmart.domain.shipments.vo.ShipmentMatchVo;
import com.ruoyi.walmart.dto.FirstLegSummaryDTO;
import com.ruoyi.walmart.mapper.Items.ItemsMapper;
import com.ruoyi.walmart.mapper.shipments.ShipmentMatchMapper;
import com.ruoyi.walmart.mapper.shipments.ShipmentStockMapper;
import com.ruoyi.walmart.service.auth.ShopAuthService;
import com.ruoyi.walmart.service.shipments.ShipmentItemService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
public class WalmartProfitReportServiceImpl implements WalmartProfitReportService {

    @Autowired
    private WalmartProfitReportMapper walmartProfitReportMapper;
    @Autowired
    private WalmartProfitReportService walmartProfitReportService;
    @Autowired
    private WalmartProfitReportDetailService walmartProfitReportDetailService;
    @Autowired
    private WalmartProfitReportDetailMapper walmartProfitReportDetailMapper;
    @Autowired
    private ItemsMapper itemsMapper;
    @Autowired
    private ShipmentItemService shipmentItemService;
    @Autowired
    private ShipmentMatchMapper shipmentMatchMapper;
    @Autowired
    private ShipmentStockMapper shipmentStockMapper;
    @Autowired
    private ShopAuthService shopAuthService;
    @Autowired
    private WalmartStockProductDetailsMapper walmartStockProductDetailsMapper;

    private static final String[] DATE_PATTERNS = {
            "yyyy/MM/dd", "yyyy-MM-dd", "MM/dd/yyyy", "dd/MM/yyyy"
    };
    /**
     * 查询walmart账单列表
     */
    @Override
    public List<WalmartProfitReport> selectPaymentMasterVotList(WalmartProfitReport walmartProfitReport) {
        // 1. 先分页查询主表数据
        List<WalmartProfitReport> reportList = walmartProfitReportMapper.selectPaymentMasterVotList(walmartProfitReport);
        if (reportList == null || reportList.isEmpty()) {
            return reportList;
        }

        // 2. 获取主表 ID 列表
        List<Long> reportIds = reportList.stream()
                .map(WalmartProfitReport::getId)
                .collect(Collectors.toList());

        // 3. 查询子表数据
        List<WalmartProfitReportDetail> detailList = walmartProfitReportDetailMapper.selectBatchProfitReportByIds(reportIds);

        Map<Long, List<WalmartProfitReportDetail>> detailMap = detailList.stream()
                .collect(Collectors.groupingBy(WalmartProfitReportDetail::getWprId));

        // 把明细设置回主表对象中
        for (WalmartProfitReport report : reportList) {
            List<WalmartProfitReportDetail> details = detailMap.getOrDefault(report.getId(), Collections.emptyList());
            report.setDetails(details);
        }

        return reportList;
    }

    /**
     * 解析CSV并获取数据
     */
    @Transactional
    public Map<String, Object> parseCSV(List<String[]> records, Long authId, String nickName, BigDecimal rate) throws ParseException {
        Map<String, Object> response = new HashMap<>();

        // 解析主表
        WalmartProfitReport report = parseMainReport(records,nickName);
        response.put("statement", report);
        // 2. 校验该店铺该时间段是否已存在报表
        int count = walmartProfitReportMapper.countByAuthIdAndDate(authId, report.getStartDate(), report.getEndDate());
        if (count > 0) {
            // 创建日期格式化工具
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

            // 格式化开始和结束日期
            String formattedStartDate = dateFormat.format(report.getStartDate());
            String formattedEndDate = dateFormat.format(report.getEndDate());

            throw new RuntimeException("该店铺：" + formattedStartDate + " 至 " + formattedEndDate + " 时间段内已上传报表，请勿重复上传！");
        }
        report.setAuthId(authId);
        report.setRate(rate);
        walmartProfitReportMapper.insert(report);

        //更新walmart货件与仓库备货关联表的收货数量
        //shipmentItemService.updateShipmentStockReceivedQty(authId);

        // 解析明细，并传入 id 进行关联
        List<WalmartProfitReportDetail> details = walmartProfitReportDetailService.parseDetails(records, report);
        walmartProfitReportDetailService.insertBatchReportDetail(details);

        return response;
    }

    /**
     * 上传walmart仓储费账单
     */
    @Override
    @Transactional
    public Map<String, Object> parseStorageFeeCSV(List<String[]> records, Long wprId, String nickName){
        Map<String, Object> result = new HashMap<>();

        // 获取账单开始和结束日期
        WalmartProfitReport wp = walmartProfitReportMapper.selectById(wprId);
        //检查和插入仓储费
        checkAndSaveStorageFee(records,wp);

        //walmart账单状态：0待导入仓储费账单，1待导入调整费账单，2待导入广告费账单，3待导入PDF账单，4待导入库存对账单，5库存未对账， 6账单未收款，7账单已收款
        wp.setStatus(Constants.WALMART_ADJUSTMENT_PENDING);
        walmartProfitReportMapper.updateById(wp);

        //  汇总调整逻辑放这里，金额不一致则均摊金额
        updateStorageFee(wp);

        result.put("success", "仓储费用解析并调整成功！");
        return result;
    }

    /**
     * 上传walmart调整费账单
     */
    @Override
    @Transactional
    public Map<String, Object> parseAdjustmentsCSV(List<String[]> records, Long wprId, String nickName){
        Map<String, Object> result = new HashMap<>();

        // 获取账单开始和结束日期
        WalmartProfitReport wp = walmartProfitReportMapper.selectById(wprId);
        //检查、插入调整费（分摊）
        checkAndSaveAdjustments(records,wp);

        //walmart账单状态：0待导入仓储费账单，1待导入调整费账单，2待导入广告费账单，3待导入PDF账单，4待导入库存对账单，5库存未对账， 6账单未收款，7账单已收款
        wp.setStatus(Constants.WALMART_ADVERTISING_PENDING);
        walmartProfitReportMapper.updateById(wp);

        result.put("success", "调整费用解析并调整成功！");
        return result;
    }
    /**
     * 上传walmart广告费账单
     */
    @Override
    @Transactional
    public Map<String, Object> parseProductAdvertisingCSV(List<String[]> records, Long wprId, String nickname) {
        Map<String, Object> result = new HashMap<>();

        // 获取账单开始和结束日期
        WalmartProfitReport wp = walmartProfitReportMapper.selectById(wprId);
        //检查、插入广告费（分摊）
        checkAndSaveProductAdvertising(records,wp);

        //walmart账单状态：0待导入仓储费账单，1待导入调整费账单，2待导入广告费账单，3待导入PDF账单，4待导入库存对账单，5库存未对账， 6账单未收款，7账单已收款
        wp.setStatus(Constants.WALMART_PDF_PENDING);
        walmartProfitReportMapper.updateById(wp);


        result.put("success", "广告费用解析并调整成功！");
        return result;
    }

    /**
     * 上传walmart 库存对账单（期末数据）
     */
    @Override
    @Transactional
    public Map<String, Object> parseInventoryReconciliationCSV(List<String[]> records, Long wprId, String nickname) {
        Map<String, Object> result = new HashMap<>();

        // 获取账单开始和结束日期
        WalmartProfitReport wp = walmartProfitReportMapper.selectById(wprId);
        //检查、调整库存
        checkAndSaveInventoryReconciliation(records,wp);

        //walmart账单状态：0待导入仓储费账单，1待导入调整费账单，2待导入广告费账单，3待导入PDF账单，4待导入库存对账单，5库存未对账， 6账单未收款，7账单已收款
        wp.setStatus(Constants.WALMART_NOT_INVENTORY_RECONCILIATION);
        walmartProfitReportMapper.updateById(wp);

        result.put("success", "库存对对账单解析并调整成功！");
        return result;
    }

    /**
     * 上传walmart 库存对账单（销售及调整数据）
     */
    @Override
    @Transactional
    public Map<String, Object> parseInventoryLoadingReconciliationCSV(List<String[]> records, Long wprId, String nickname) {
        Map<String, Object> result = new HashMap<>();

        // 获取账单开始和结束日期
        WalmartProfitReport wp = walmartProfitReportMapper.selectById(wprId);
        //检查、调整库存
        checkAndSaveInventoryLoadingReconciliation(records,wp);
        wp.setStatus("0");
        walmartProfitReportMapper.updateById(wp);

        result.put("success", "库存对对账单解析并调整成功！");
        return result;
    }

    /**
     * walmart匹配列表（成本、头程）
     */
    @Override
    public List<ShipmentMatchVo> selectShipmentMatchVotList(ShipmentMatchVo shipmentMatch) {
        return shipmentMatchMapper.selectShipmentMatchVotList(shipmentMatch);
    }
    /**
     * 上传二次销售商品
     */
    @Override
    @Transactional
    public void uploadWalmartShipmentStockAgain(MultipartFile file, Long authId) {
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0); // 读取第一个工作表
            int rowCount = sheet.getPhysicalNumberOfRows();
            if (rowCount <= 1) {
                throw new RuntimeException("Excel 数据为空");
            }

            for (int i = 1; i < rowCount; i++) { // 从第二行开始读取（第一行是表头）
                Row row = sheet.getRow(i);
                if (row == null || isRowEmpty(row)) { // **跳过空行**
                    continue;
                }

                //校验sku
                String sku = getCellValue(row.getCell(0)).trim();//sku
                validateString(sku, i , "SKU");
                Items item = itemsMapper.selectItemsByGtinAndAuthId(authId, "", "", sku);
                if(item == null){
                    throw new RuntimeException("第"+ ( i + 1 )+"行，SKU格式有误：" + sku+"，未找到对应的商品链接");
                }
                //校验qty
                String secondColumnValue = getCellValue(row.getCell(1)).trim(); // 获取第二列的值
                if (StringUtils.isBlank(secondColumnValue)) {
                    throw new RuntimeException("第" + (i + 1) + "行，第2列值不能为空");
                }
                if (!StringUtils.isNumeric(secondColumnValue)) {
                    throw new RuntimeException("第" + (i + 1) + "行，第2列值必须是数字，当前值：" + secondColumnValue);
                }

                ShipmentStock shipmentStock = new ShipmentStock();
                shipmentStock.setProductCost(BigDecimal.ZERO);
                shipmentStock.setFirstLeg(BigDecimal.ZERO);
                shipmentStock.setSku(sku);
                shipmentStock.setGtin(item.getGtin());
                shipmentStock.setQty(Integer.valueOf(secondColumnValue));
                shipmentStock.setMatchedQty(0);
                shipmentStock.setUnmatchedQty(Integer.valueOf(secondColumnValue));
                shipmentStock.setActualDeliveryDate(LocalDateTime.of(2024, 1, 1, 1, 1, 1, 1));
                shipmentStock.setAuthId(authId);
                shipmentStockMapper.insert(shipmentStock);

            }
        } catch (Exception e) {
            throw new RuntimeException("导入 Excel 失败：" + e.getMessage());
        }
    }
    /**
     * 初始化数据
     */
    @Override
    @Transactional
    public void uploadInitData(MultipartFile file) {
        try (InputStream is = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(is)) {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = sheet.iterator();

            // Skip header
            if (iterator.hasNext()) iterator.next();

            while (iterator.hasNext()) {
                Row row = iterator.next();
                String shopName = getCellValue(row.getCell(0));
                String _shopName = getCellValue(row.getCell(10));
                ShopAuth shopAuth = shopAuthService.selectShopAuthByShopName(shopName);
                ShopAuth _shopAuth = shopAuthService.selectShopAuthByShopName(_shopName);
                if(shopAuth == null || (_shopAuth == null && StringUtils.isNotEmpty(_shopName))){
                    throw new RuntimeException("第" + (row.getRowNum()+ 1) + "行，获取店铺信息失败");
                }

                String gtin = getCellValue(row.getCell(1));
                String sku = getCellValue(row.getCell(2));
                double qty = row.getCell(3).getNumericCellValue();
                double cost = row.getCell(4).getNumericCellValue();
                double headCost = row.getCell(5).getNumericCellValue();

                if(qty != 0){
                    ShipmentStock shipmentStock = new ShipmentStock();
                    shipmentStock.setId(IdWorker.getId());
                    BigDecimal costBD     = BigDecimal.valueOf(cost);
                    BigDecimal headCostBD = BigDecimal.valueOf(headCost);
                    BigDecimal qtyBD      = BigDecimal.valueOf(qty);
                    BigDecimal avgCost = costBD.divide(qtyBD, 2, RoundingMode.UP);
                    BigDecimal avgHead = headCostBD.divide(qtyBD, 2, RoundingMode.UP);
                    shipmentStock.setProductCost(avgCost);
                    shipmentStock.setFirstLeg(avgHead);

                    Items item = itemsMapper.selectItemsByGtinAndAuthId(shopAuth.getAuthId(), gtin, "", sku);
                    if(item == null){
                        throw new RuntimeException("第" + (row.getRowNum()+ 1) + "行，获取商品信息失败");
                    }
                    shipmentStock.setSku(sku);
                    shipmentStock.setGtin(gtin);
                    shipmentStock.setAuthId(shopAuth.getAuthId());
                    shipmentStock.setQty((int) qty);
                    shipmentStock.setUnmatchedQty((int) qty);
                    shipmentStock.setActualDeliveryDate(LocalDateTime.of(2024, 1, 1, 1, 1, 1, 1));
                    shipmentStockMapper.insert(shipmentStock);
                }
                if(_shopAuth != null && StringUtils.isNotEmpty(_shopName)){
                    String _shipmentId = getCellValue(row.getCell(9));
                    String _gtin = getCellValue(row.getCell(11));
                    String _sku = getCellValue(row.getCell(12));
                    double _qty = row.getCell(13).getNumericCellValue();
                    double _cost = row.getCell(14).getNumericCellValue();
                    double _headCost = row.getCell(15).getNumericCellValue();

                    ShipmentStock _shipmentStock = new ShipmentStock();
                    _shipmentStock.setId(IdWorker.getId());
                    BigDecimal _costBD     = BigDecimal.valueOf(_cost);
                    BigDecimal _headCostBD = BigDecimal.valueOf(_headCost);
                    BigDecimal _qtyBD      = BigDecimal.valueOf(_qty);
                    BigDecimal _avgCost = _costBD.divide(_qtyBD, 2, RoundingMode.UP);
                    BigDecimal _avgHead = _headCostBD.divide(_qtyBD, 2, RoundingMode.UP);
                    _shipmentStock.setProductCost(_avgCost);
                    _shipmentStock.setFirstLeg(_avgHead);

                    Items item = itemsMapper.selectItemsByGtinAndAuthId(_shopAuth.getAuthId(), _gtin, "", _sku);
                    if(item == null){
                        throw new RuntimeException("第" + (row.getRowNum()+ 1) + "行，获取商品信息失败");
                    }
                    _shipmentStock.setSku(_sku);
                    _shipmentStock.setGtin(_gtin);
                    _shipmentStock.setShipmentId(_shipmentId);
                    _shipmentStock.setAuthId(_shopAuth.getAuthId());
                    _shipmentStock.setQty((int) _qty);
                    _shipmentStock.setUnmatchedQty((int) _qty);
                    _shipmentStock.setActualDeliveryDate(LocalDateTime.of(2024, 2, 2, 2, 2, 2, 2));
                    shipmentStockMapper.insert(_shipmentStock);
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 解析主表数据（汇总数据）
     */
    @Transactional
    public  WalmartProfitReport parseMainReport(List<String[]> records, String nickName) throws ParseException {
        if (records.size() < 3) {
            throw new RuntimeException("CSV 数据行数不足，请检查文件内容是否正确！");
        }

        // 校验表头字段是否包含必须字段
        String[] header = records.get(0); // 第一行是表头
        List<String> requiredHeaders = Arrays.asList("Transaction Type", "Transaction Description", "Amount Type", "Amount");
        for (String h : requiredHeaders) {
            boolean found = Arrays.stream(header).anyMatch(col -> col.trim().equalsIgnoreCase(h));
            if (!found) {
                throw new RuntimeException("CSV 表头缺少必要字段: " + h + "，请确认导出的表格是否为官方格式！");
            }
        }

        // 解析汇总行（假设在第3行）
        String[] summaryRow = records.get(2);
        if (summaryRow.length < 3) {
            throw new RuntimeException("CSV 报表头部数据不足，请检查起止日期和打款金额！");
        }
        SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy"); // CSV 中的日期格式
        String startDateStr  = summaryRow[0];
        String endDateStr = summaryRow[1];
        String payoutStr = summaryRow[2];

        Date startDate = dateFormat.parse(startDateStr);

        Date endDate = dateFormat.parse(endDateStr);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(endDate);
        calendar.add(Calendar.DATE, -1);  // 往前推一天
        // 获取新的日期
        Date newDate = calendar.getTime();
        endDate = newDate;

        BigDecimal payoutAmount = new BigDecimal(payoutStr);

        // 自动生成报表编号（WALM+日期+编号）
        String reportCode = walmartProfitReportService.generateReportCode();

        // 汇总字段初始化
        BigDecimal storageFeeTotal = BigDecimal.ZERO;
        BigDecimal adjustmentsTotal = BigDecimal.ZERO;
        BigDecimal disposalFeeTotal = BigDecimal.ZERO;
        BigDecimal advertisingFeeTotal = BigDecimal.ZERO;
        BigDecimal semFeeTotal = BigDecimal.ZERO;
        BigDecimal inventoryTransferFeeTotal = BigDecimal.ZERO;
        BigDecimal otherFeeTotal = BigDecimal.ZERO;

        // 构建表头索引（建议全局使用）
        Map<String, Integer> headerIndex = new HashMap<>();
        for (int i = 0; i < header.length; i++) {
            headerIndex.put(header[i].trim(), i);
        }

        // 遍历记录（从第三行开始，如果汇总行固定为第三行，则从第三行开始解析汇总数据）
        for (int i = 2; i < records.size(); i++) {
            String[] row = records.get(i);
            String transType = row[headerIndex.get("Transaction Type")];
            String desc = row[headerIndex.get("Transaction Description")];
            String amountType = row[headerIndex.get("Amount Type")];
            String amountStr = row[headerIndex.get("Amount")];
            BigDecimal amount = parseBigDecimal(amountStr);

            if (desc == null || desc.trim().isEmpty()) {
                continue; // 跳过空白行
            }else{
                // 先trim去除前后空格
                desc = desc.trim();
                // 移除首尾的双引号（如果存在）
                if (desc.startsWith("\"") && desc.endsWith("\"")) {
                    desc = desc.substring(1, desc.length() - 1);
                }
                // 再次trim，因为双引号内可能还有空格
                desc = desc.trim();
            }
            // 验证desc是否在有效范围内
            if (!VALID_DESCRIPTIONS.contains(desc)) {
                throw new IllegalArgumentException(
                        String.format("数据解析错误: '%s' 不在预期的交易描述范围内，请联系管理人员核查", desc)
                );
            }

            if ("Service Fee".equalsIgnoreCase(transType) && "WFS StorageFee".equalsIgnoreCase(desc)) {
                storageFeeTotal = storageFeeTotal.add(amount);
            }
            if ("Adjustment".equalsIgnoreCase(transType)
                    && (amountType.contains("WFS Inventory Fee/Reimbursement"))) {
                adjustmentsTotal = adjustmentsTotal.add(amount);
            }
            if ("WFS RC_InventoryDisposalFee".equalsIgnoreCase(desc)) {
                disposalFeeTotal = disposalFeeTotal.add(amount);
            }
            if ("Walmart Product Advertising".equalsIgnoreCase(desc)) {
                advertisingFeeTotal = advertisingFeeTotal.add(amount);
            }
            if ("SEM Marketing".equalsIgnoreCase(desc)) {
                semFeeTotal = semFeeTotal.add(amount);
            }
            if ("WFS InventoryTransferFee".equalsIgnoreCase(desc)) {
                inventoryTransferFeeTotal = inventoryTransferFeeTotal.add(amount);
            }
            if ("Funds withheld to cover future refunds".equalsIgnoreCase(desc)||"WFS PrepServiceFee".equalsIgnoreCase(desc)
                    ||"WFS LongTermStorageFee".equalsIgnoreCase(desc)||"Reserve credited back".equalsIgnoreCase(desc)
                    ||"Seller Triggered Refund".equals(desc)) {
                otherFeeTotal = otherFeeTotal.add(amount);
            }

        }

        WalmartProfitReport report = new WalmartProfitReport();
        report.setReportCode(reportCode);
        report.setStartDate(startDate);
        report.setEndDate(endDate);
        report.setPayoutAmount(payoutAmount);
        report.setStorageFeeTotal(storageFeeTotal);
        report.setAdjustmentsTotal(adjustmentsTotal);
        report.setDisposalFeeTotal(disposalFeeTotal);
        report.setInventoryTransferFeeTotal(inventoryTransferFeeTotal);
        report.setAdvertisingFeeTotal(advertisingFeeTotal);
        report.setSemFeeTotal(semFeeTotal);
        report.setOtherFeeTotal(otherFeeTotal);
        report.setCreatedBy(nickName);

        return report;
    }

    /**
     * 检查和插入仓储费
     */
    @Transactional
    public void checkAndSaveStorageFee(List<String[]> records, WalmartProfitReport wp) {

        if (records == null || records.size() < 6) {
            throw new RuntimeException("CSV 文件格式不正确，至少包含 6 行");
        }

        // 验证日期
        findDateRow(records, wp);

        // 第 7 行才是真正的字段头（索引 6）
        String[] headers = records.get(6);
        int gtinIdx = -1, skuIdx = -1, storageFeeIdx = -1;

        for (int i = 0; i < headers.length; i++) {
            String header = headers[i].trim();
            if ("Partner GTIN".equalsIgnoreCase(header) || "GTIN".equalsIgnoreCase(header)) gtinIdx = i;
            if ("Vendor SKU".equalsIgnoreCase(header) || "SKU".equalsIgnoreCase(header)) skuIdx = i;
            if ("Storage Fee for Selected Time Period".equalsIgnoreCase(header)|| "Final storage fee".equalsIgnoreCase(header)) storageFeeIdx = i;
        }

        if (gtinIdx == -1 || skuIdx == -1 || storageFeeIdx == -1) {
            throw new RuntimeException("CSV 表头缺少必要字段");
        }

        // 从第 9 行（索引 8）开始遍历数据
        for (int i = 8; i < records.size(); i++) {
            String[] record = records.get(i);
            try {
                // 处理可能带有Excel公式标记的数据
                String gtin = cleanExcelFormula(record[gtinIdx]);
                String sku = cleanExcelFormula(record[skuIdx]);
                //查询查询关联本地商品的sku
                String localSku = shipmentStockMapper.getLocalSkuByItemSku(sku);
                if(localSku != null){
                    sku = localSku;
                }
                BigDecimal wfsStorageFee = new BigDecimal(record[storageFeeIdx]);

                LambdaQueryWrapper<WalmartProfitReportDetail> query = new LambdaQueryWrapper<>();
                query.eq(WalmartProfitReportDetail::getWprId, wp.getId());
                query.eq(WalmartProfitReportDetail::getGtin, gtin);
                query.eq(WalmartProfitReportDetail::getSku, sku);

                WalmartProfitReportDetail detail = walmartProfitReportDetailMapper.selectOne(query);

                if (detail != null) {
                    detail.setWfsStorageFee(wfsStorageFee);
                    walmartProfitReportDetailMapper.updateById(detail);
                } else {
                    WalmartProfitReportDetail newDetail = new WalmartProfitReportDetail();
                    newDetail.setId(IdWorker.getId());
                    newDetail.setWprId(wp.getId());
                    newDetail.setSku(sku);
                    newDetail.setGtin(gtin);
                    newDetail.setWfsStorageFee(wfsStorageFee);
                    walmartProfitReportDetailMapper.insert(newDetail);
                }
            } catch (Exception e) {
                throw new RuntimeException("第 " + (i + 1) + " 行处理失败：" + e.getMessage(), e);
            }
        }
    }

    /**
     * 检查和插入调整费
     */
    @Transactional
    public void checkAndSaveAdjustments(List<String[]> records, WalmartProfitReport wp) {

        // 1. 校验 CSV 日期
        validateCSVDate(records, wp);

        // 2. 获取字段索引（第4行是表头）
        String[] headers = records.get(3);
        int gtinIdx = -1;
        int typeIdx = -1;
        int amountIdx = -1;

        for (int i = 0; i < headers.length; i++) {
            String h = headers[i].trim();
            if ("Partner GTIN".equalsIgnoreCase(h)) gtinIdx = i;
            if ("Transaction Type".equalsIgnoreCase(h)) typeIdx = i;
            if ("Net Payable".equalsIgnoreCase(h)) amountIdx = i;
        }

        if (gtinIdx == -1 || typeIdx == -1 || amountIdx == -1) {
            throw new RuntimeException("CSV 缺少必要字段（GTIN, Transaction Type, Net Payable）");
        }

        // 3. 遍历数据：汇总 GTIN 调整金额，记录空 GTIN 金额
        Map<String, BigDecimal> adjMap = new HashMap<>();
        Map<String, BigDecimal> rcMap = new HashMap<>();
        Map<String, BigDecimal> chMap = new HashMap<>();//Charge金额
        Map<String, BigDecimal> itMap = new HashMap<>();//inventoryTransfer库存转移金额
        BigDecimal unknownAdj = BigDecimal.ZERO;
        BigDecimal unknownRc = BigDecimal.ZERO;
        BigDecimal unknownCh = BigDecimal.ZERO;
        BigDecimal unknownIt = BigDecimal.ZERO;

        for (int i = 4; i < records.size(); i++) {
            String[] row = records.get(i);
            if (row.length <= Math.max(gtinIdx, Math.max(amountIdx, typeIdx))) continue;

            String gtin = cleanExcelFormula(row[gtinIdx]);
            String type = row[typeIdx].trim();
            String amountStr = row[amountIdx].trim();

            if (type.isEmpty() || amountStr.isEmpty()) continue;

            // 验证desc是否在有效范围内
            if (!VALID_TRANSACTION_TYPE.contains(type)) {
                throw new IllegalArgumentException(
                        String.format("数据解析错误: '%s' 不在预期的交易类型范围内，请联系管理人员核查", type)
                );
            }

            // 只处理目标类型
            if (!Arrays.asList("DamageInWarehouse", "LostInventory", "FoundInventory", "Refund","RC_InventoryDisposalFee","Charge","InventoryTransferFee").contains(type)) {
                continue;
            }

            BigDecimal amount = new BigDecimal(amountStr);

            if ("RC_InventoryDisposalFee".equalsIgnoreCase(type)) {
                if (gtin.isEmpty()) unknownRc = unknownRc.add(amount);
                else rcMap.merge(gtin, amount, BigDecimal::add);
            } else if (Arrays.asList("DamageInWarehouse", "LostInventory", "FoundInventory", "Refund").contains(type)) {
                if (gtin.isEmpty()) unknownAdj = unknownAdj.add(amount);
                else adjMap.merge(gtin, amount, BigDecimal::add);
            }else if ("Charge".equalsIgnoreCase(type)) {
                if (gtin.isEmpty()) unknownCh = unknownCh.add(amount);
                else chMap.merge(gtin, amount, BigDecimal::add);
            }else if ("InventoryTransferFee".equalsIgnoreCase(type)) {
                if (gtin.isEmpty()) unknownIt = unknownIt.add(amount);
                else itMap.merge(gtin, amount, BigDecimal::add);
            }

        }

        // 4. 分摊空 GTIN 数据（RC） - 使用 distributeWithCompensation
        if (unknownRc.compareTo(BigDecimal.ZERO) != 0 && !rcMap.isEmpty()) {
            FeeDistributor.distributeWithCompensation(
                    new ArrayList<>(rcMap.entrySet()),
                    unknownRc,
                    Map.Entry::getValue,
                    Map.Entry::getValue,
                    (e, v) -> rcMap.put(e.getKey(), e.getValue().add(v)),
                    2,
                    new BigDecimal("0.01"),
                    true
            );
        }

        // 分摊空 GTIN 的金额 - 使用 distributeWithCompensation
        if (unknownAdj.compareTo(BigDecimal.ZERO) != 0 && !adjMap.isEmpty()) {
            FeeDistributor.distributeWithCompensation(
                    new ArrayList<>(adjMap.entrySet()),
                    unknownAdj,
                    Map.Entry::getValue,        // 获取原值
                    Map.Entry::getValue,        // 获取现值（用于补差）
                    (e, v) -> adjMap.put(e.getKey(), e.getValue().add(v)),  // 设置新值
                    2, // 保留两位小数
                    new BigDecimal("0.01"),  // 容差阈值
                    true                         //是否需要修正方向
            );
        }

        // 分摊空 GTIN 的金额 Charge
        if (unknownCh.compareTo(BigDecimal.ZERO) != 0 && !chMap.isEmpty()) {
            FeeDistributor.distributeWithCompensation(
                    new ArrayList<>(chMap.entrySet()),
                    unknownCh,
                    Map.Entry::getValue,        // 获取原值
                    Map.Entry::getValue,        // 获取现值（用于补差）
                    (e, v) -> chMap.put(e.getKey(), e.getValue().add(v)),  // 设置新值
                    2, // 保留两位小数
                    new BigDecimal("0.01"),  // 容差阈值
                    true                         //是否需要修正方向
            );
        }

        // 分摊空 GTIN 的金额 inventoryTransfer
        if (unknownIt.compareTo(BigDecimal.ZERO) != 0 && !itMap.isEmpty()) {
            FeeDistributor.distributeWithCompensation(
                    new ArrayList<>(itMap.entrySet()),
                    unknownIt,
                    Map.Entry::getValue,        // 获取原值
                    Map.Entry::getValue,        // 获取现值（用于补差）
                    (e, v) -> itMap.put(e.getKey(), e.getValue().add(v)),  // 设置新值
                    2, // 保留两位小数
                    new BigDecimal("0.01"),  // 容差阈值
                    true                         //是否需要修正方向
            );
        }
        // 5. 批量查询现有数据
        Set<String> allGtins = new HashSet<>();
        allGtins.addAll(adjMap.keySet());
        allGtins.addAll(rcMap.keySet());
        allGtins.addAll(chMap.keySet());
        allGtins.addAll(itMap.keySet());

        // 过滤掉空字符串
        allGtins = allGtins.stream()
                .filter(gtin -> gtin != null && !gtin.trim().isEmpty())
                .collect(Collectors.toSet());

        // 批量查询现有的明细
        List<WalmartProfitReportDetail> existingDetails;
        if (allGtins.isEmpty()) {
            existingDetails = new ArrayList<>();
        } else {
            existingDetails = walmartProfitReportDetailMapper.selectList(
                    new LambdaQueryWrapper<WalmartProfitReportDetail>()
                            .eq(WalmartProfitReportDetail::getWprId, wp.getId())
                            .in(WalmartProfitReportDetail::getGtin, allGtins)
            );
        }

        // 使用 Map 存储现有的明细，方便查找
        Map<String, WalmartProfitReportDetail> existingDetailsMap = existingDetails.stream()
                .collect(Collectors.toMap(WalmartProfitReportDetail::getGtin, detail -> detail));

        List<WalmartProfitReportDetail> toInsert = new ArrayList<>();
        List<WalmartProfitReportDetail> toUpdate = new ArrayList<>();

        // 将数据合并到更新和插入的列表中
        for (String gtin : allGtins) {
            BigDecimal adj = adjMap.getOrDefault(gtin, BigDecimal.ZERO).setScale(2, RoundingMode.HALF_UP);
            BigDecimal rc = rcMap.getOrDefault(gtin, BigDecimal.ZERO).negate().setScale(2, RoundingMode.HALF_UP);
            BigDecimal ch = chMap.getOrDefault(gtin, BigDecimal.ZERO).negate().setScale(2, RoundingMode.HALF_UP);
            BigDecimal it = itMap.getOrDefault(gtin, BigDecimal.ZERO).negate().setScale(2, RoundingMode.HALF_UP);

            WalmartProfitReportDetail detail = existingDetailsMap.get(gtin);
            if (detail != null) {
                // 更新现有记录
                detail.setWfsAdjustments(adj);
                detail.setWfsRcFee(rc);
                detail.setWfsChargeFee(ch);
                detail.setWfsInventoryTransferFee(it);
                toUpdate.add(detail);
            } else {
                // 插入新记录
                Items items = itemsMapper.selectItemsByGtinAndAuthId(wp.getAuthId(), gtin, "", "");
                if (items == null) {
                    throw new RuntimeException("GTIN：" + gtin + "，未找到对应的商品链接");
                }
                WalmartProfitReportDetail newDetail = new WalmartProfitReportDetail();
                newDetail.setId(IdWorker.getId());
                newDetail.setWprId(wp.getId());
                newDetail.setSku(items.getSku());
                newDetail.setGtin(gtin);
                newDetail.setWfsAdjustments(adj);
                newDetail.setWfsInventoryTransferFee(it);
                newDetail.setWfsRcFee(rc);
                newDetail.setWfsChargeFee(ch);
                toInsert.add(newDetail);
            }
        }

        // 6. 做主表金额的误差补偿（用于和主表对齐）
        FeeDistributor.distributeWithCompensation(
                toUpdate,
                wp.getAdjustmentsTotal(), // 主表值
                WalmartProfitReportDetail::getWfsAdjustments,
                WalmartProfitReportDetail::getWfsAdjustments,
                WalmartProfitReportDetail::setWfsAdjustments,
                5,
                new BigDecimal("0.01"),
                true
        );

        // 7.批量更新
        if (!toUpdate.isEmpty()) walmartProfitReportDetailService.updateBatchById(toUpdate);
        if (!toInsert.isEmpty()) {
            // 使用Stream检查空GTIN
            List<String> emptySkuGtins = toInsert.stream()
                    .filter(detail -> StringUtils.isBlank(detail.getSku())) // 过滤SKU为空的
                    .map(detail -> detail.getGtin()) // 获取对应的GTIN
                    .distinct()
                    .collect(Collectors.toList());

            if (!emptySkuGtins.isEmpty()) {
                String message = String.format("该店铺GTIN:%s的SKU为空，请联系运营人员核对是否配对",
                        String.join(",", emptySkuGtins));
                throw new RuntimeException(message);
            }
            walmartProfitReportDetailService.insertBatchReportDetail(toInsert);
        }
    }

    /**
     * 检查和插入广告费
     */
    @Transactional
    public void checkAndSaveProductAdvertising(List<String[]> records, WalmartProfitReport wp) {
        if (records == null || records.size() < 3) {
            throw new RuntimeException("广告费CSV数据格式错误");
        }

        // 1. 校验时间段
        String periodLine = records.get(0)[0].trim(); // e.g. "Period Requested: Mar 08, 2025 - Mar 21, 2025"
        Pattern pattern = Pattern.compile("Period Requested:\\s*(\\w+ \\d{2}, \\d{4})\\s*-\\s*(\\w+ \\d{2}, \\d{4})");
        Matcher matcher = pattern.matcher(periodLine);
        if (!matcher.find()) {
            throw new RuntimeException("广告CSV日期格式不正确");
        }

        DateFormat inputFormat = new SimpleDateFormat("MMM dd, yyyy", Locale.ENGLISH);
        Date startDate, endDate;
        try {
            startDate = inputFormat.parse(matcher.group(1));
            endDate = inputFormat.parse(matcher.group(2));
        } catch (ParseException e) {
            throw new RuntimeException("CSV中广告日期解析失败");
        }

        if (!startDate.equals(wp.getStartDate()) || !endDate.equals(wp.getEndDate())) {
            throw new RuntimeException("CSV中广告时间段与账单不匹配！");
        }

        // 2. 字段索引
        String[] headers = records.get(1);
        int itemIdIdx = -1, adSpendIdx = -1;
        for (int i = 0; i < headers.length; i++) {
            String h = headers[i].trim();
            if ("Item Id".equalsIgnoreCase(h)) itemIdIdx = i;
            if ("Ad Spend".equalsIgnoreCase(h)) adSpendIdx = i;
        }

        if (itemIdIdx == -1 || adSpendIdx == -1) {
            throw new RuntimeException("缺少必要字段（Item Id / Ad Spend）");
        }

        // 3. 数据处理
        Map<String, BigDecimal> advertisingMap = new HashMap<>();
        BigDecimal unknownAmount = BigDecimal.ZERO;

        for (int i = 2; i < records.size(); i++) {
            String[] row = records.get(i);
            if (row.length <= Math.max(itemIdIdx, adSpendIdx)) continue;

            String itemId = row[itemIdIdx].trim();
            String spendStr = row[adSpendIdx].trim();

            if (itemId.isEmpty() || spendStr.isEmpty()) continue;

            spendStr = spendStr.replace("$", "").replace(",", "").trim();
            BigDecimal adSpend = new BigDecimal(spendStr);

            Items item = itemsMapper.selectItemsByGtinAndAuthId(wp.getAuthId(), "", itemId, "");

            if (item != null) {
                String gtin = item.getGtin();
                advertisingMap.merge(gtin, adSpend, BigDecimal::add);
            } else {
                unknownAmount = unknownAmount.add(adSpend);
            }
        }

        // 广告值全部转为负数（表示费用方向）
        advertisingMap.replaceAll((k, v) -> v.negate());

        // 4. 分摊 unknownAmount 到已有广告费
        if (unknownAmount.compareTo(BigDecimal.ZERO) > 0 && !advertisingMap.isEmpty()) {
            FeeDistributor.distributeWithCompensation(
                    new ArrayList<>(advertisingMap.entrySet()),
                    unknownAmount,
                    Map.Entry::getValue,
                    Map.Entry::getValue,
                    (entry, value) -> advertisingMap.put(entry.getKey(), entry.getValue().add(value)),
                    2,
                    new BigDecimal("0.01"),
                    true
            );
        }

        // 5.使用主表广告费总额，做比例补差
        BigDecimal targetTotal = Optional.ofNullable(wp.getAdvertisingFeeTotal()).orElse(BigDecimal.ZERO);
        if (targetTotal.abs().compareTo(BigDecimal.ZERO) > 0 && !advertisingMap.isEmpty()) {
            FeeDistributor.distributeWithCompensation(
                    new ArrayList<>(advertisingMap.entrySet()),
                    targetTotal,
                    Map.Entry::getValue,
                    Map.Entry::getValue,
                    (entry, value) -> advertisingMap.put(entry.getKey(), value),
                    2,
                    new BigDecimal("0.01"),
                    false
            );
        }

        // 6 广告费负数处理 + 插入/更新明细表
        List<WalmartProfitReportDetail> toInsert = new ArrayList<>();
        List<WalmartProfitReportDetail> toUpdate = new ArrayList<>();

        for (Map.Entry<String, BigDecimal> entry : advertisingMap.entrySet()) {
            String gtin = entry.getKey();
            BigDecimal adTotal = entry.getValue().setScale(5, RoundingMode.HALF_UP);

            LambdaQueryWrapper<WalmartProfitReportDetail> query = new LambdaQueryWrapper<>();
            query.eq(WalmartProfitReportDetail::getWprId, wp.getId());
            query.eq(WalmartProfitReportDetail::getGtin, gtin);

            WalmartProfitReportDetail detail = walmartProfitReportDetailMapper.selectOne(query);
            if (detail != null) {
                detail.setProductAdvertising(adTotal);
                toUpdate.add(detail);
            } else {
                Items item = itemsMapper.selectItemsByGtinAndAuthId(wp.getAuthId(), gtin, "", "");
                if (item == null) {
                    throw new RuntimeException("找不到 GTIN：" + gtin + " 对应的商品信息，无法插入广告费明细");
                }
                WalmartProfitReportDetail newDetail = new WalmartProfitReportDetail();
                newDetail.setId(IdWorker.getId());
                newDetail.setWprId(wp.getId());
                newDetail.setSku(item.getSku());
                newDetail.setGtin(gtin);
                newDetail.setProductAdvertising(adTotal);
                toInsert.add(newDetail);
            }
        }

        // 6. 批量更新 & 插入
        if (!toUpdate.isEmpty()) walmartProfitReportDetailService.updateBatchById(toUpdate);
        if (!toInsert.isEmpty()) {
            // 使用Stream检查空GTIN
            List<String> emptySkuGtins = toInsert.stream()
                    .filter(detail -> StringUtils.isBlank(detail.getSku())) // 过滤SKU为空的
                    .map(detail -> detail.getGtin()) // 获取对应的GTIN
                    .distinct()
                    .collect(Collectors.toList());

            if (!emptySkuGtins.isEmpty()) {
                String message = String.format("该店铺GTIN:%s的SKU为空，请联系运营人员核对是否配对",
                        String.join(",", emptySkuGtins));
                throw new RuntimeException(message);
            }
            walmartProfitReportDetailService.insertBatchReportDetail(toInsert);
        }

    }
    /**
     * 检查和调整库存（期末数据）
     */
    @Transactional
    public void checkAndSaveInventoryReconciliation(List<String[]> records, WalmartProfitReport wp) {
        if (records == null || records.size() < 3) {
            throw new RuntimeException("库存对账单CSV数据格式错误");
        }

        // =========================
        // 0. 找到各列在数组中的下标
        // =========================
        String[] header = records.get(0);
        Map<String, Integer> idx = new HashMap<>();
        for (int i = 0; i < header.length; i++) {
            idx.put(header[i].trim(), i);
        }
        Integer startIdx  = idx.get("Start Date");
        Integer endIdx    = idx.get("End Date");
        // 商品标识列：这里演示用 GTIN，如果你要用 SKU，需要改成 "Vendor (Seller) SKU" 或者对应列名
        Integer skuIdx    = idx.get("Vendor (Seller) SKU");
        Integer endingIdx = idx.get("Ending Quantity");
        if (startIdx == null || endIdx == null || skuIdx == null || endingIdx == null) {
            throw new RuntimeException("CSV 缺少必须的列（Start Date/End Date/GTIN/Ending Quantity）");
        }

        // =========================
        // 1. 校验时间段（取第二行数据）
        // =========================
        DateFormat dfDash = new SimpleDateFormat("yyyy-MM-dd");
        DateFormat dfSlash = new SimpleDateFormat("yyyy/MM/dd");
        Date csvStart, csvEnd;
        try {
            String[] firstDataRow = records.get(2);
            String startDateStr = firstDataRow[startIdx].trim();
            String endDateStr = firstDataRow[endIdx].trim();

            // 尝试使用两种格式解析
            csvStart = parseWithMultipleFormats(startDateStr, dfDash, dfSlash);
            csvEnd = parseWithMultipleFormats(endDateStr, dfDash, dfSlash);
        } catch (ParseException e) {
            throw new RuntimeException("CSV 日期格式解析失败（支持yyyy-MM-dd和yyyy/MM/dd）", e);
        }
        if (!csvStart.equals(wp.getStartDate()) || !csvEnd.equals(wp.getEndDate())) {
            throw new RuntimeException("CSV时间段与账单不匹配！"
                    + " CSV[" + dfDash.format(csvStart) + " - " + dfDash.format(csvEnd) + "]  vs  "
                    + "Bill[" + dfDash.format(wp.getStartDate()) + " - " + dfDash.format(wp.getEndDate()) + "]");
        }

        // =========================
        // 2. 汇总 Ending Quantity
        // =========================
        Map<String, Integer> endingQtyMap = new HashMap<>();
        // 从第三行（索引2）开始才是真正的 SKU 明细
        for (int i = 2; i < records.size(); i++) {
            String[] row = records.get(i);
            String sku = row[skuIdx].trim();
            //查询查询关联本地商品的sku
            String localSku = shipmentStockMapper.getLocalSkuByItemSku(sku);
            if(localSku != null){
                sku = localSku;
            }
            if (sku.isEmpty()) continue;

            String val = row[endingIdx].trim();
            int qty;
            try {
                qty = Integer.parseInt(val);
            } catch (NumberFormatException e) {
                // 非数字就跳过
                continue;
            }
            endingQtyMap.merge(sku, qty, Integer::sum);
        }

        LambdaQueryWrapper<ShipmentStock> query = new LambdaQueryWrapper<>();
        query.eq(ShipmentStock::getStatus, 0L);
        query.eq(ShipmentStock::getAuthId, wp.getAuthId());
        query.isNotNull(ShipmentStock::getActualDeliveryDate);
        query.orderByAsc(ShipmentStock::getActualDeliveryDate);
        List<ShipmentStock> allStocks = shipmentStockMapper.selectList(query);

        for (Map.Entry<String, Integer> entry : endingQtyMap.entrySet()) {
            String sku = entry.getKey();
            int endingQty = entry.getValue(); // 例如 24

            // 找出该 SKU 的所有记录
            List<ShipmentStock> matchedStocks = allStocks.stream()
                    .filter(stock -> sku.equals(stock.getSku()))
                    .collect(Collectors.toList());

            // 计算 SKU 总 unmatchedQty
            int totalUnmatchedQty = matchedStocks.stream()
                    .mapToInt(stock -> stock.getUnmatchedQty() + stock.getApprovedQty())
                    .sum();

            // 总差值
            int diff = endingQty - totalUnmatchedQty;

            QueryWrapper<WalmartProfitReportDetail> _query = new QueryWrapper<>();
            _query.eq("wpr_id", wp.getId()).eq("sku", sku);

            WalmartProfitReportDetail detail = walmartProfitReportDetailMapper.selectOne(_query);
            if (detail != null && diff != 0 ) {
                detail.setDiffQty(-diff);
                walmartProfitReportDetailMapper.updateById(detail);
            }


            for (ShipmentStock stock : matchedStocks) {
                if (diff == 0) break;

                int unmatchedQty = stock.getUnmatchedQty();

                // 当前记录可处理的 assignQty，不能超过 unmatchedQty，不能超过 diff
                int assignQty;

                if (diff > 0) {
                    assignQty = diff;//Math.min(unmatchedQty, diff);  // 正差值：最多只能匹配 unmatchedQty
                    stock.setApprovedQty(0);
                    //插入一条记录
                    ShipmentStock ss = new ShipmentStock();
                    ss.setId(IdWorker.getId());
                    ss.setProductCost(stock.getProductCost());
                    ss.setFirstLeg(stock.getFirstLeg());
                    ss.setSku(sku);
                    ss.setGtin(stock.getGtin());
                    ss.setShipmentId(generateCode());
                    ss.setQty(diff);
                    ss.setUnmatchedQty(diff);
                    ss.setAuthId(wp.getAuthId());
                    ss.setActualDeliveryDate(Constants.ACTUAL_DELIVERY_DATE);
                    shipmentStockMapper.insert(ss);
                    stock.setShipmentId(ss.getShipmentId());
                    diff = 0;
                } else {
                    assignQty = Math.max(-unmatchedQty, diff); // 负差值：最多只能减少 unmatchedQty
                    stock.setApprovedQty(assignQty);
                    diff -= assignQty; // 更新剩余差值
                }

                // 插入 match 记录
                ShipmentMatch newMatch = new ShipmentMatch();
                newMatch.setId(IdWorker.getId());
                newMatch.setWprId(wp.getId());
                newMatch.setProductCost(stock.getProductCost());
                newMatch.setFirstLeg(stock.getFirstLeg());
                newMatch.setShipmentId(stock.getShipmentId());  // 记得 stock 对象要带上它的 shipmentId
                newMatch.setSku(sku);
                newMatch.setGtin(stock.getGtin());
                newMatch.setMatchedQty(-assignQty);
                newMatch.setMatchedTime(LocalDateTime.now());
                newMatch.setAuthId(wp.getAuthId());
                newMatch.setIsMatch("1");
                shipmentMatchMapper.insert(newMatch);

                shipmentStockMapper.updateById(stock);
            }
        }
        //更新成本和头程
        updateProductCostAndFreight(wp);
    }
    /**
     * 检查和调整库存（销售及调整）
     */
    @Transactional
    public void checkAndSaveInventoryLoadingReconciliation(List<String[]> records, WalmartProfitReport wp) {
        if (records == null || records.size() < 3) {
            throw new RuntimeException("库存对账单CSV数据格式错误");
        }

        // =========================
        // 0. 找到各列在数组中的下标
        // =========================
        String[] header = records.get(0);
        Map<String, Integer> idx = new HashMap<>();
        for (int i = 0; i < header.length; i++) {
            idx.put(header[i].trim(), i);
        }
        Integer startIdx  = idx.get("Start Date");
        Integer endIdx    = idx.get("End Date");
        // 商品标识列：这里演示用 GTIN，如果你要用 SKU，需要改成 "Vendor (Seller) SKU" 或者对应列名
        Integer skuIdx    = idx.get("Vendor (Seller) SKU");
        Integer lostIdx = idx.get("Lost");
        Integer foundIdx = idx.get("Found");
        Integer undeliveredIdx = idx.get("Undelivered");
        Integer transferredIdx = idx.get("Transferred");
        Integer rtsIdx = idx.get("Returned to seller");
        Integer removedIdx = idx.get("Removed");

        if (startIdx == null || endIdx == null || skuIdx == null || lostIdx == null
                || foundIdx ==null || undeliveredIdx ==null|| transferredIdx ==null|| rtsIdx ==null|| removedIdx ==null
        ) {
            throw new RuntimeException("CSV 缺少必须的列（Start Date/End Date/GTIN/Lost/Found/Undelivered/Transferred/Returned to seller/Removed）");
        }

        // =========================
        // 1. 校验时间段（取第二行数据）
        // =========================
        DateFormat dfDash = new SimpleDateFormat("yyyy-MM-dd");
        DateFormat dfSlash = new SimpleDateFormat("yyyy/MM/dd");
        Date csvStart, csvEnd;
        try {
            String[] firstDataRow = records.get(2);
            String startDateStr = firstDataRow[startIdx].trim();
            String endDateStr = firstDataRow[endIdx].trim();

            // 尝试使用两种格式解析
            csvStart = parseWithMultipleFormats(startDateStr, dfDash, dfSlash);
            csvEnd = parseWithMultipleFormats(endDateStr, dfDash, dfSlash);
        } catch (ParseException e) {
            throw new RuntimeException("CSV 日期格式解析失败（支持yyyy-MM-dd和yyyy/MM/dd）", e);
        }
        if (!csvStart.equals(wp.getStartDate()) || !csvEnd.equals(wp.getEndDate())) {
            throw new RuntimeException("CSV时间段与账单不匹配！"
                    + " CSV[" + dfDash.format(csvStart) + " - " + dfDash.format(csvEnd) + "]  vs  "
                    + "Bill[" + dfDash.format(wp.getStartDate()) + " - " + dfDash.format(wp.getEndDate()) + "]");
        }

        // =========================
        // 2. 汇总 Quantity
        // =========================
        Map<SkuGtinKey, Integer> adjustQtyMap = new HashMap<>();
        // 从第三行（索引2）开始才是真正的 SKU 明细
        for (int i = 2; i < records.size(); i++) {
            String[] row = records.get(i);
            String sku = row[skuIdx].trim();

            String lost = row[lostIdx].trim();
            String found = row[foundIdx].trim();
            String undelivered = row[undeliveredIdx].trim();
            String transferred = row[transferredIdx].trim();
            String rts = row[rtsIdx].trim();
            String removed = row[removedIdx].trim();
            int qty;
            try {
                qty = Integer.parseInt(lost)+Integer.parseInt(found)+Integer.parseInt(undelivered)+Integer.parseInt(transferred)+Integer.parseInt(rts)+Integer.parseInt(removed);
            } catch (NumberFormatException e) {
                // 非数字就跳过
                continue;
            }

            if(qty<=0){
                continue;
            }

            Items item = itemsMapper.selectItemsByGtinAndAuthId(wp.getAuthId(), "", "", sku);
            if(item == null){
                throw new RuntimeException("SKU："+sku+"，获取链接失败");
            }
            String gtin = item.getGtin();

            //查询查询关联本地商品的sku
            String localSku = shipmentStockMapper.getLocalSkuByItemSku(sku);
            if(localSku != null){
                sku = localSku;
            }
            if (sku.isEmpty()) continue;

            SkuGtinKey key = new SkuGtinKey(sku, gtin);
            adjustQtyMap.merge(key, qty, Integer::sum);
        }

        //查询出所有可以匹配的数据
        LambdaQueryWrapper<ShipmentStock> query = new LambdaQueryWrapper<>();
        query.eq(ShipmentStock::getStatus, 0L);
        query.eq(ShipmentStock::getAuthId, wp.getAuthId());
        query.isNotNull(ShipmentStock::getActualDeliveryDate);
        query.orderByAsc(ShipmentStock::getActualDeliveryDate);
        List<ShipmentStock> allStocks = shipmentStockMapper.selectList(query);

        for (Map.Entry<SkuGtinKey, Integer> entry : adjustQtyMap.entrySet()) {
            SkuGtinKey key = entry.getKey();
            String sku = key.getSku();
            String gtin = key.getGtin();
            int adjustQty = entry.getValue();

            if (adjustQty < 0) {
                //处理负数库存（扣减逻辑）
                handleNegativeAdjustment(sku,gtin, adjustQty, allStocks, wp);
            } else if (adjustQty > 0) {
                // 处理正数库存（新增逻辑）
                handlePositiveAdjustment(sku, gtin, adjustQty, allStocks, wp);
            }

            if (adjustQty != 0) {
                walmartProfitReportDetailMapper.update(
                        null,
                        new LambdaUpdateWrapper<WalmartProfitReportDetail>()
                                .eq(WalmartProfitReportDetail::getWprId, wp.getId())
                                .eq(WalmartProfitReportDetail::getGtin, gtin)
                                .set(WalmartProfitReportDetail::getAdjustQty, -adjustQty)
                );
            }

        }
        //更新成本和头程
        updateProductCostAndFreight(wp);

    }
    /**
     * 更新明细的头程和成本
     */
    public void updateProductCostAndFreight(WalmartProfitReport wp){
        // 查询每个 SKU 的加权平均成本和头程费用
        List<CostFreightPair> avgList = shipmentMatchMapper.selectAvgCostFreightBySku(wp.getAuthId(),wp.getId());

        // 将查询结果转化为 Map<String, CostFreightPair>
        Map<SkuGtinKey, CostFreightPair> result = new HashMap<>();

        for (CostFreightPair agg : avgList) {
            // 使用 SKU+GTIN 作为key
            SkuGtinKey key = new SkuGtinKey(agg.getSku(), agg.getGtin());
            result.put(key, agg); // 直接使用查询结果，因为已经包含了所有字段
        }
        List<WalmartProfitReportDetailUpdate> updateList = new ArrayList<>();

        for (Map.Entry<SkuGtinKey, CostFreightPair> entry : result.entrySet()) {
            SkuGtinKey key = entry.getKey();
            CostFreightPair pair = entry.getValue();

            WalmartProfitReportDetailUpdate update = new WalmartProfitReportDetailUpdate();
            update.setWprId(wp.getId());
            update.setSku(key.getSku());
            update.setGtin(key.getGtin());
            update.setProductCost(pair.getProductCost());
            update.setFirstLeg(pair.getFirstLeg());

            updateList.add(update);
        }
        // 调用 Mapper 执行批量更新
        walmartProfitReportDetailMapper.batchUpdateProductCostAndFreight(updateList);
    }

    /**
     * 汇总调整逻辑放这里，金额不一致则均摊金额
     */
    public void updateStorageFee(WalmartProfitReport wp){
        List<WalmartProfitReportDetail> detailList = walmartProfitReportDetailMapper.selectList(
                new LambdaQueryWrapper<WalmartProfitReportDetail>().eq(WalmartProfitReportDetail::getWprId, wp.getId())
        );

        BigDecimal targetFee = Optional.ofNullable(wp.getStorageFeeTotal()).orElse(BigDecimal.ZERO);

        FeeDistributor.distributeWithCompensation(
                detailList,
                targetFee,
                WalmartProfitReportDetail::getWfsStorageFee,   // 获取原值（用于占比计算）
                WalmartProfitReportDetail::getWfsStorageFee,   // 获取现值（用于补差）
                WalmartProfitReportDetail::setWfsStorageFee,   // 设置值
                5,                                             // 精度保留五位
                new BigDecimal("0.00001"),                 // 容差阈值
                false                                          //是否需要修正方向
        );
        // 最终更新数据库
        walmartProfitReportDetailService.updateBatchById(detailList);
    }

    /**
     * 更新关联货件配对表，匹配表，账单头程
     */
    @Override
    @Transactional
    public void updateShipmentStockFirstLeg(String stockId){

        // 1. 根据 stockId 查询所有相关的产品详情记录
        List<WalmartStockProductDetails> stockProductDetailsList = walmartStockProductDetailsMapper.selectList(
                new LambdaQueryWrapper<WalmartStockProductDetails>()
                        .eq(WalmartStockProductDetails::getStockId, stockId)
        );

        List<ShipmentStock> updateList = new ArrayList<>();
        List<ShipmentMatch> updateMatchList = new ArrayList<>();

        // 2. 遍历每条记录计算 firstLeg
        for (WalmartStockProductDetails stockProductDetails : stockProductDetailsList) {
            // 检查数据有效性
            if (stockProductDetails.getShipmentsProductCount() == null ||
                    stockProductDetails.getShipmentsProductCount() == 0 ||
                    stockProductDetails.getFreight() == null) {
                // 没有详情或分母为 0，则跳过
                continue;
            }
            // 计算 firstLeg
            BigDecimal freight = stockProductDetails.getFreight();
            BigDecimal shipmentsProductCount = BigDecimal.valueOf(stockProductDetails.getShipmentsProductCount());
            BigDecimal firstLeg = freight.divide(shipmentsProductCount, 2, RoundingMode.HALF_UP);

            String sku = stockProductDetails.getSku();
            //用于历史下错的订单sku
            //String originalSku = SKU_MAPPING.getOrDefault(stockProductDetails.getSku(), stockProductDetails.getSku());

            // 查询对应的 ShipmentStock 记录
            ShipmentStock shipmentStock = shipmentStockMapper.selectOne(
                    new LambdaQueryWrapper<ShipmentStock>()
                            .eq(ShipmentStock::getStockId, stockId)
                            .eq(ShipmentStock::getSku, sku)
            );

            if (shipmentStock != null) {
                shipmentStock.setFirstLeg(firstLeg);
                updateList.add(shipmentStock);

                // 查询对应的 ShipmentMatch 记录（可能有多条）
                List<ShipmentMatch> matchList = shipmentMatchMapper.selectList(
                        new LambdaQueryWrapper<ShipmentMatch>()
                                .eq(ShipmentMatch::getShipmentId, shipmentStock.getShipmentId())
                                .eq(ShipmentMatch::getSku,sku)
                );

                for (ShipmentMatch match : matchList) {
                    match.setFirstLeg(firstLeg); // 假设 ShipmentMatch 实体有这个字段
                    updateMatchList.add(match);
                }
            }
        }
        // 3. 批量更新
        if (!updateList.isEmpty()) {
            shipmentStockMapper.updateStockFirstLegBatchById(updateList);
        }
        if (!updateMatchList.isEmpty()) {
            shipmentMatchMapper.updateMatchFirstLegBatchById(updateMatchList);
        }

        //4.更新账单里面的头程
        updateProfitReportFirstLeg(stockId);

    }
    //4.更新账单里面的头程
    private void updateProfitReportFirstLeg(String stockId) {
        // 查询所有相关的 ShipmentMatch 记录，按 wpr_id 和 sku 分组汇总头程费用
        List<FirstLegSummaryDTO> firstLegSummary = shipmentMatchMapper.selectFirstLegSummaryByStockIdAndSku(stockId);

        for (FirstLegSummaryDTO summary : firstLegSummary) {
            String wprId = summary.getWprId();
            WalmartProfitReport wp = walmartProfitReportMapper.selectById(wprId);
            //更新成本和头程
            updateProductCostAndFreight(wp);
        }

    }

    /**
     * 自动生成唯一的报表编号，格式：WALM2025040301
     */
    @Override
    public String generateReportCode() {
        // 生成前缀：WALM20250403
        String dateStr = new SimpleDateFormat("yyyyMMdd").format(new Date());
        String prefix = "WALM" + dateStr;

        // 从数据库中查出最大编号（WALM2025040305）
        String maxCode = walmartProfitReportMapper.selectMaxReportCodeLike(prefix);

        int nextSeq = 1;
        if (maxCode != null && maxCode.length() >= (prefix.length() + 2)) {
            try {
                String seqStr = maxCode.substring(prefix.length());
                nextSeq = Integer.parseInt(seqStr) + 1;
            } catch (NumberFormatException e) {
                // 可加日志打印异常内容
            }
        }

        return prefix + String.format("%02d", nextSeq); // eg: WALM2025040306
    }

    private static String generateCode() {
        // 获取当前时间，格式为：年月日时分秒
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String timeStr = sdf.format(new Date());

        // 生成四位随机数，不足补零
        Random random = new Random();
        int randNum = random.nextInt(10000); // 0 到 9999
        String randStr = String.format("%04d", randNum);

        // 拼接最终代码
        return timeStr + randStr + "DM";
    }
    private BigDecimal parseBigDecimal(String str) {
        try {
            return new BigDecimal(str.trim());
        } catch (Exception e) {
            return BigDecimal.ZERO;
        }
    }

    //校验 CSV 日期
    private void validateCSVDate(List<String[]> records, WalmartProfitReport wp) {
        try {
//            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
//            String[] dateRow = records.get(2);
//            Date csvStart = dateFormat.parse(dateRow[0].trim());
//            Date csvEnd = dateFormat.parse(dateRow[1].trim());

            String[] dateRow = records.get(2);
            String startDateStr = dateRow[0].trim();
            String endDateStr = dateRow[1].trim();
            String datePattern = startDateStr.contains("/") ? "yyyy/MM/dd" : "yyyy-MM-dd";
            DateFormat dateFormat = new SimpleDateFormat(datePattern);
            Date csvStart = dateFormat.parse(startDateStr);
            Date csvEnd = dateFormat.parse(endDateStr);

            if (!csvStart.equals(wp.getStartDate()) || !csvEnd.equals(wp.getEndDate())) {
                throw new RuntimeException("CSV 中的日期与账单日期不一致！");
            }
        } catch (Exception e) {
            throw new RuntimeException("CSV 日期解析失败：" + e.getMessage());
        }
    }
    /**
     * 获取单元格数据
     */
    private String getCellValue(Cell cell) {
        if (cell == null) return "";
        if (cell.getCellType() == CellType.NUMERIC) {
            double value = cell.getNumericCellValue();
            if (value == Math.floor(value)) {  // 判断是否是整数
                return String.valueOf((long) value); // 转换为 long，去掉 .0
            } else {
                return String.valueOf(value); // 仍然返回小数
            }
        }
        return cell.getStringCellValue().trim();
    }
    /**
     * 检查 Excel 行是否为空
     */
    private boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK &&
                    (cell.getCellType() != CellType.STRING || !cell.getStringCellValue().trim().isEmpty())) {
                return false; // 发现非空单元格，行不是空的
            }
        }
        return true; // 整行是空的
    }
    /**
     * 校验字符串是否为空
     */
    private void validateString(String value, int rowIndex, String fieldName) {
        if (value == null || value.trim().isEmpty()) {
            throw new RuntimeException("第 " + (rowIndex+1) + " 行，" + fieldName + " 不能为空！");
        }
    }
    // 辅助方法：尝试多种日期格式
    private Date parseWithMultipleFormats(String dateStr, DateFormat... formats) throws ParseException {
        ParseException lastException = null;
        for (DateFormat df : formats) {
            try {
                return df.parse(dateStr);
            } catch (ParseException e) {
                lastException = e;
                // 继续尝试下一个格式
            }
        }
        // 如果所有格式都失败，抛出最后一个异常
        throw lastException;
    }

    // 处理Excel公式格式的辅助方法
    private String cleanExcelFormula(String value) {
        if (value == null) return "";
        value = value.trim();

        // 处理Excel公式格式：="xxx"
        if (value.startsWith("=\"") && value.endsWith("\"")) {
            return value.substring(2, value.length() - 1);
        }

        // 处理简单公式格式：=xxx
        if (value.startsWith("=")) {
            return value.substring(1);
        }

        return value;
    }

    // 定义有效的交易描述白名单
    private static final Set<String> VALID_DESCRIPTIONS = new HashSet<>(Arrays.asList(
            "WFS Return Shipping fee",
            "Deposited in PINGPONG account",
            "Keep-it refund",
            "Purchase",
            "Return Refund",
            "SEM Marketing",
            "Walmart Product Advertising",
            "WFS Charge",
            "WFS DamageInWarehouse",
            "WFS FoundInventory",
            "WFS Fulfillment fee",
            "WFS InventoryTransferFee",
            "WFS LostInventory",
            "WFS RC_InventoryDisposalFee",
            "WFS Refund",
            "WFS StorageFee",
            "Funds withheld to cover future refunds",
            "WFS PrepServiceFee",
            "WFS LongTermStorageFee",
            "Reserve credited back",
            "Seller Triggered Refund"
    ));
    // 定义有效的交易类型白名单
    private static final Set<String> VALID_TRANSACTION_TYPE = new HashSet<>(Arrays.asList(
            "DamageInWarehouse",
            "FoundInventory",
            "FulfillmentFee",
            "LostInventory",
            "InventoryTransferFee",
            "RC_InventoryDisposalFee",
            "Refund",
            "ReturnShippingFee",
            "StorageFee",
            "Charge",
            "PrepServiceFee",
            "LongTermStorageFee"

    ));
    //处理负数库存（扣减逻辑）
    private void handleNegativeAdjustment(String sku, String gtin ,int adjustQty, List<ShipmentStock> allStocks, WalmartProfitReport wp) {
        int remainingToMatch = -adjustQty;

        List<ShipmentStock> stocksToMatch = allStocks.stream()
                .filter(stock -> sku.equals(stock.getSku()) && stock.getUnmatchedQty() > 0
                        && stock.getActualDeliveryDate() != null )
                .sorted(Comparator.comparing(ShipmentStock::getActualDeliveryDate))
                .collect(Collectors.toList());

        for (ShipmentStock stock : stocksToMatch) {
            int availableQty = stock.getUnmatchedQty();
            if (availableQty <= 0) continue;

            int matchQty = Math.min(availableQty, remainingToMatch);
            stock.setMatchedQty(stock.getMatchedQty() + matchQty);
            stock.setUnmatchedQty(stock.getUnmatchedQty() - matchQty);
            if (stock.getUnmatchedQty() == 0) {
                stock.setStatus(1L); // 已匹配
            }

            shipmentStockMapper.updateById(stock); // 假设你有这个 Mapper

            ShipmentMatch match = new ShipmentMatch();
            match.setId(IdWorker.getId());
            match.setWprId(wp.getId());
            match.setSku(sku);
            match.setGtin(gtin);
            match.setProductCost(stock.getProductCost());
            match.setFirstLeg(stock.getFirstLeg());
            match.setShipmentId(stock.getShipmentId());
            match.setMatchedTime(LocalDateTime.now());
            match.setMatchedQty(matchQty);
            match.setAuthId(wp.getAuthId());
            match.setRemark("库存扣减");
            match.setIsMatch("0");

            shipmentMatchMapper.insert(match);

            remainingToMatch -= matchQty;
            if (remainingToMatch <= 0) break;
        }

        if (remainingToMatch > 0) {
            // 可以记录日志或抛出异常警告：库存不足以匹配全部调整
        }
    }
    // 处理正数库存（新增逻辑）
    private void handlePositiveAdjustment(String sku, String gtin, int adjustQty, List<ShipmentStock> allStocks, WalmartProfitReport wp) {
        ShipmentStock base = allStocks.stream()
                .filter(stock -> sku.equals(stock.getSku()) && stock.getActualDeliveryDate() != null)
                .findFirst()
                .orElse(null);

        if (base == null) {
            // 无参考记录，无法创建新库存
            return;
        }

        ShipmentStock newStock = new ShipmentStock();
        newStock.setId(IdWorker.getId());
        newStock.setSku(sku);
        newStock.setGtin(gtin);
        newStock.setProductCost(base.getProductCost());
        newStock.setFirstLeg(base.getFirstLeg());
        newStock.setQty(adjustQty);
        newStock.setMatchedQty(adjustQty);
        newStock.setUnmatchedQty(0);
        newStock.setStatus(1L);
        newStock.setStockId(null);
        newStock.setShipmentId(null);
        newStock.setAuthId(wp.getAuthId());

        LocalDateTime newTime = base.getActualDeliveryDate().plusSeconds(1);
        newStock.setActualDeliveryDate(newTime);

        shipmentStockMapper.insert(newStock);

        ShipmentMatch match = new ShipmentMatch();
        match.setId(IdWorker.getId());
        match.setWprId(wp.getId());
        match.setSku(sku);
        match.setGtin(gtin);
        match.setProductCost(base.getProductCost());
        match.setFirstLeg(base.getFirstLeg());
        match.setShipmentId(null);
        match.setMatchedTime(LocalDateTime.now());
        match.setMatchedQty(-adjustQty);
        match.setAuthId(wp.getAuthId());
        match.setRemark("新增库存");
        match.setIsMatch("0");

        shipmentMatchMapper.insert(match);
    }


    private Date parseDate(String dateStr) {
        if (dateStr == null || dateStr.trim().isEmpty()) return null;
        for (String pattern : DATE_PATTERNS) {
            try {
                return new SimpleDateFormat(pattern).parse(dateStr.trim());
            } catch (ParseException e) {
                // 继续尝试下一个格式
            }
        }
        throw new RuntimeException("无法解析日期格式: " + dateStr);
    }

    private void findDateRow(List<String[]> records, WalmartProfitReport wp) {
        for (int i = 0; i < Math.min(records.size(), 6); i++) { // 限制扫描前 5 行
            String[] row = records.get(i);
            for (String cell : row) {
                try {
                    Date date = parseDate(cell);
                    if (date != null) {
                        // 假设第一个解析成功的日期是 startDate，第二个是 endDate
                        Date csvStartDate = date;
                        Date csvEndDate = parseDate(row.length > 1 ? row[1] : null);
                        if (csvEndDate == null) {
                            // 如果只有 startDate，尝试下一行找 endDate
                            if (i + 1 < records.size()) {
                                csvEndDate = parseDate(records.get(i + 1)[0]);
                            }
                        }
                        if (csvStartDate.equals(wp.getStartDate()) && csvEndDate.equals(wp.getEndDate())) {
                            return; // 日期匹配，退出
                        } else {
                            throw new RuntimeException("CSV 中的日期与账单日期不匹配！");
                        }
                    }
                } catch (Exception e) {
                    // 继续检查其他单元格
                }
            }
        }
        throw new RuntimeException("无法找到有效的日期行");
    }

}

